--/
/*
      多阶用途表
*/
CREATE PROCEDURE sp_getMultiRank    
        @bomTop varchar(50),
        @materiel varchar(50),
        @marks varchar(50)
        as
        declare @bomCode varchar(50) 
        declare @bomTopCode varchar(50)
        declare @materielCode varchar(50)
        declare @pCode varchar(50)
        declare @counts varchar(50)
        declare @bomTopVersion varchar(50)
        declare @orders int
        declare @pName varchar(50)
        declare @attritionRate varchar(50)
        declare @fixedAttritionRate    varchar(50)
        declare @processName    varchar(50)
        declare @materialProcess    varchar(50)  
        declare @rank varchar(10)
        create table #tempTable (
                       rank varchar(50), 
                       materielCode varchar(50),
                       materielName varchar(50),
                       bomTopVersion varchar(50),
                       pName varchar(50),
                       counts  varchar(50),
                       effectiveDate varchar(50),
                       expiryDate varchar(50),
                       attritionRate varchar(50),
                       fixedAttritionRate varchar(50),
                       processName varchar(50),
                       materialProcess varchar(50),
                       basisNum varchar(10),
                       auditStatusName varchar(50),
                       clienFeed varchar(50),
                       manufacturer varchar(50),
                       auditStatusCode varchar(50),
                       bomCode varchar(50),     
                       pCode varchar(50),
                       amount varchar(50),
                       mark varchar(5),
                        orders  int
                )
                 create table #singleTable (
                       rank varchar(50), 
                       materielCode varchar(50),
                       materielName varchar(50),
                       bomTopVersion varchar(50),
                       pName varchar(50),
                       counts  varchar(50),
                       effectiveDate varchar(50),
                       expiryDate varchar(50),
                       attritionRate varchar(50),
                       fixedAttritionRate varchar(50),
                       processName varchar(50),
                       materialProcess varchar(50),
                       basisNum varchar(10),
                       auditStatusName varchar(50),
                       clienFeed varchar(50),
                       manufacturer varchar(50),
                       auditStatusCode varchar(50),
                       bomCode varchar(50),     
                       pCode varchar(50),
                       amount varchar(50),
                       mark varchar(5),
                        orders  int
                )
                print 1
                
            insert into #tempTable  exec sp_getRecursiveTreeData  @bomTop,@materiel,@marks
          
            declare @num int
            set @num = 0
              
            declare c_recursive cursor for select bomCode,pCode,materielCode,bomTopVersion,counts,orders,pName,processName,materialProcess,rank  from #tempTable
             open c_recursive
             
             fetch next from c_recursive into @bomCode,@pCode,@materielCode,@bomTopVersion,@counts,@orders,@pName,@processName,@materialProcess,@rank
             while(@@fetch_status = 0)
             begin
                        set @num = @num + 1
                        print @num 
                        print  @bomCode
                     
                        insert into #singleTable select * from #tempTable where bomCode = @bomCode and orders = @orders
                         declare @materielCodeS varchar(50) 
                        declare @bomPCode varchar(50)
                        declare @substituteCode varchar(50)
                        declare @substituteName varchar(50)
                        declare @effectiveDate varchar(50)
                        declare @expiryDate varchar(50)
                        declare @substituteTimes varchar(20)
                        
                        declare c_subsSing cursor for select  materielCode,bomPCode,substituteCode,substituteName,effectiveDate,expiryDate,substituteTimes from b_substitute_info where status = '1' 
                         open c_subsSing
                         
                        fetch next from c_subsSing into @materielCodeS,@bomPCode,@substituteCode,@substituteName,@effectiveDate,@expiryDate,@substituteTimes
                        while (@@fetch_status = 0)
                        begin
                                declare @c varchar(20)
                                set @c = cast(cast(@counts as decimal(10,1)) * cast(@substituteTimes as decimal(10,1)) as decimal(10,1))
                                
                                if(@bomPCode = @pCode and @materielCode = @materielCodes)
                                begin
                                
                                        insert into  #singleTable select @rank,@substituteCode,@substituteName,@bomTopVersion,@pName,@c,@effectiveDate,@expiryDate,'','',@processName,@materialProcess,'','','','','','','','','n','' from b_substitute_info where status = '1' and bomPCode = @bomPCode and substituteCode = @substituteCode
                                end
                                  fetch next from c_subsSing  into @materielCodeS,@bomPCode,@substituteCode,@substituteName,@effectiveDate,@expiryDate,@substituteTimes
                        end
                        print 'colse c_subsSing start'
                        
                        close c_subsSing
                        
                        deallocate c_subsSing
                         print 'colse c_subsSing end'
                       fetch next from c_recursive into @bomCode,@pCode,@materielCode,@bomTopVersion,@counts,@orders,@pName,@processName,@materialProcess ,@rank                    
             end
              print 'colse c_recursive start'
              
             close c_recursive
             
             deallocate c_recursive
             print 'colse c_recursive end'
             
        select * from #singleTable
/
